﻿using DotNetCore.CAP;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using NP.BPMReportPlatform.AnalysisInstance.Dtos;
using NP.BPMReportPlatform.BaseData.Dto;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.Common.Dtos;
using NP.BPMReportPlatform.Contract.Dtos;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.EmailHelper;
using NP.BPMReportPlatform.Entities.BPM_Engine;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.RecurringJobs.Dto;
using NP.BPMReportPlatform.Utilities;
using NP.BPMReportPlatform.Utils;
using NP.Enterprise.Utils.Http;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SqlSugar;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using static NP.BPMReportPlatform.Common.TreeBuildTool;
using static NP.BPMReportPlatform.Contract.Dtos.ContractEnum;

namespace NP.BPMReportPlatform.Common
{
    public class CommonService : BPMReportPlatformAppService, ICommonService
    {
        private readonly SqlSugarClient _dbPlatform;
        private readonly SqlSugarClient _dbEngine;
        private readonly IEmailAPPService _emailAPPService;
        private ICapPublisher _capBus;
        private readonly INPRestHttpHelper _nPRestHttp;
        private readonly IConfiguration _configuration;

        public CommonService(ISqlSugarAppService sqlSugarAppService, IEmailAPPService emailAPPService, ICapPublisher capPublisher,
            INPRestHttpHelper nPRestHttp, IConfiguration configuration)
        {
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _emailAPPService = emailAPPService;
            _capBus = capPublisher;
            _nPRestHttp = nPRestHttp;
            _configuration = configuration;
        }

        /// <summary>
        /// 根据条件查询甲方
        /// </summary>
        /// <param name="searchDto"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<ItemPairDto>> GetAllCorporateList(CommonSearchDto searchDto)
        {
            RefAsync<int> totalCount = 0;
            var res = await _dbPlatform.Queryable<CorporateEntity>()
                .Where(b => b.Status == 1 && !string.IsNullOrEmpty(b.AuthorizationOrganizeId))
                .WhereIF(!string.IsNullOrEmpty(searchDto.CodeOrName), b => b.CorporateCode.Contains(searchDto.CodeOrName) || b.CorporateName.Contains(searchDto.CodeOrName))
                .Select(b => new ItemPairDto()
                {
                    Value = b.CorporateCode,
                    Label = b.CorporateName,
                    ExtendFiled = b.CorporatePostCode,
                    ExtendFiled1 = b.CorporatePhone,
                    ExtendFiled2 = b.CorporateAddress,
                    ExtendFiled3 = b.LegalPerson
                }).ToPageListAsync(searchDto.PageIndex, searchDto.PageSize, totalCount);
            return new PagedResultDto<ItemPairDto>()
            {
                TotalCount = totalCount,
                Items = res
            };
        }

        /// <summary>
        /// 根据条件查询城市数据
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public async Task<List<TreeNodes>> GetAllCities(string name)
        {
            var citys = await _dbPlatform.Queryable<CityAreaEntity>()
                .WhereIF(!string.IsNullOrEmpty(name), p => p.Name.Contains(name))
                .Select(p => new TreeNodes() { value = p.Code, label = p.Name, pid = p.ParentCode, disabled = false })
                .OrderBy(t => t.value)
                .ToListAsync();
            var res = new TreeBuild<TreeNodes>().Build(citys, string.Empty);
            res?.ForEach(p =>
            {
                if (p.options == null || p.options.Count <= 0)
                {
                    p.options = new List<TreeNodes>();
                    p.options.Add(new TreeNodes()
                    {
                        options = new List<TreeNodes>(),
                        disabled = false,
                        value = p.value,
                        label = p.label,
                        pid = p.value
                    });
                }
            });
            return res;
        }

        /// <summary>
        /// 获取对应人员的权限下的人员 ER
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="ignoreAdmin">忽略admin权限</param>
        /// <returns></returns>
        public List<string> GetUserByRoleFilter(string workNumber, RelationshipTypeEnum type, bool ignoreAdmin = false)
        {
            // 获取权限，先看是否有所有数据权限，这边通过额外配置，跟角色没关系
            // 默认添加管理员权限
            var adminroles = _dbPlatform.Queryable<Dictionaries>()
                             .Where(t => t.TypeCode == "ADMINROLE").Select(t => t.Value).ToList();
            if (adminroles.Contains(workNumber) && !ignoreAdmin)
            {
                return new List<string>() { "9999999999" };
            }

            if (type == RelationshipTypeEnum.HRBP)
            {
                List<string> worknumbers = new List<string>();

                // 权限过滤
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => a.UserId == workNumber && b.RoleCode == "HRBPPermission").Select((a, b) => b.DeptCode).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    worknumbers = _dbPlatform.Queryable<UserExtension>().Where(t => deptcodes.Contains(t.Objid_1) || deptcodes.Contains(t.Objid_2)
                    || deptcodes.Contains(t.Objid_3) || deptcodes.Contains(t.Objid_4) || deptcodes.Contains(t.Objid_5) || deptcodes.Contains(t.Objid_6)
                    || deptcodes.Contains(t.Objid_7) || deptcodes.Contains(t.Objid_8))
                        .Select(t => t.Sapemployeeid).ToList();
                }

                return worknumbers;
            }
            else if (type == RelationshipTypeEnum.HRBPHead)
            {
                List<string> worknumbers = new List<string>();

                // 权限过滤
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => a.UserId == workNumber && b.RoleCode == "HRBP HEAD DOA").Select((a, b) => b.DeptCode).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    worknumbers = _dbPlatform.Queryable<UserExtension>().Where(t => deptcodes.Contains(t.Objid_1) || deptcodes.Contains(t.Objid_2)
                    || deptcodes.Contains(t.Objid_3) || deptcodes.Contains(t.Objid_4) || deptcodes.Contains(t.Objid_5) || deptcodes.Contains(t.Objid_6)
                    || deptcodes.Contains(t.Objid_7) || deptcodes.Contains(t.Objid_8))
                        .Select(t => t.Sapemployeeid).ToList();
                }

                return worknumbers;
            }
            else if (type == RelationshipTypeEnum.HRBPPerformance)
            {
                List<string> worknumbers = new List<string>();

                // 权限过滤
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => a.UserId == workNumber && b.RoleCode == "HRBP Performance").Select((a, b) => b.DeptCode).ToList();
                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    worknumbers = _dbPlatform.Queryable<UserExtension>().Where(t => deptcodes.Contains(t.Objid_1) || deptcodes.Contains(t.Objid_2)
                    || deptcodes.Contains(t.Objid_3) || deptcodes.Contains(t.Objid_4) || deptcodes.Contains(t.Objid_5) || deptcodes.Contains(t.Objid_6)
                    || deptcodes.Contains(t.Objid_7) || deptcodes.Contains(t.Objid_8))
                        .Select(t => t.Sapemployeeid).ToList();
                }

                return worknumbers;
            }
            else if (type == RelationshipTypeEnum.培训专员)
            {
                List<string> worknumbers = new List<string>();
                // 权限过滤
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                                .Where((a, b) => a.UserId == workNumber && (b.RoleCode == "HRBP TD DOA" || b.RoleCode == "ADMIN TD DOA"))
                                .Select((a, b) => b.DeptCode).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    worknumbers = _dbPlatform.Queryable<UserExtension>().Where(t => deptcodes.Contains(t.Objid_1) || deptcodes.Contains(t.Objid_2)
                    || deptcodes.Contains(t.Objid_3) || deptcodes.Contains(t.Objid_4) || deptcodes.Contains(t.Objid_5) || deptcodes.Contains(t.Objid_6)
                    || deptcodes.Contains(t.Objid_7) || deptcodes.Contains(t.Objid_8))
                        .Select(t => t.Sapemployeeid).ToList();
                }

                return worknumbers;
            }
            else if (type == RelationshipTypeEnum.培训专员Head)
            {
                List<string> worknumbers = new List<string>();
                // 权限过滤
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => a.UserId == workNumber && b.RoleCode == "HRBP HEAD TD DOA").Select((a, b) => b.DeptCode).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    worknumbers = _dbPlatform.Queryable<UserExtension>().Where(t => deptcodes.Contains(t.Objid_1) || deptcodes.Contains(t.Objid_2)
                    || deptcodes.Contains(t.Objid_3) || deptcodes.Contains(t.Objid_4) || deptcodes.Contains(t.Objid_5) || deptcodes.Contains(t.Objid_6)
                    || deptcodes.Contains(t.Objid_7) || deptcodes.Contains(t.Objid_8))
                        .Select(t => t.Sapemployeeid).ToList();
                }

                return worknumbers;
            }

            return _dbPlatform.Queryable<SFOtherApproverEntity>()
                .Where(p => p.RelationshipType == type.GetHashCode().ToString())
                .Where(p => p.RelUserId == workNumber)
                .Select(p => p.UserId).ToList();
        }

        /// <summary>
        /// 获取指定人对应的HRBP
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="ignoreAdmin">忽略admin权限</param>
        /// <returns></returns>
        public List<string> GetUserHRBPFilter(string workNumber, RelationshipTypeEnum type)
        {
            // 获取对应的HRBP
            if (type == RelationshipTypeEnum.HRBP)
            {
                List<string> worknumbers = new List<string>();

                // 找对应部门
                var allDeptFullPathCodes = _dbPlatform.Queryable<Users>().Where(t => t.WorkNumber == workNumber).First()?.FullPathCode?.Split("_").Reverse().ToList();

                // 找到所有的人
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => allDeptFullPathCodes.Contains(b.DeptCode) && b.RoleCode == "HRBPPermission").Select((a, b) => new { a.UserId, b.DeptCode }).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    foreach (var deptcode in allDeptFullPathCodes)
                    {
                        if (deptcodes.Where(t => t.DeptCode == deptcode).Count() > 0)
                        {
                            worknumbers = deptcodes.Where(t => t.DeptCode == deptcode).Select(t => t.UserId).ToList();
                            break;
                        }
                    }
                }

                return worknumbers;
            }
            else if (type == RelationshipTypeEnum.培训专员)
            {
                List<string> worknumbers = new List<string>();

                // 找对应部门
                var allDeptFullPathCodes = _dbPlatform.Queryable<Users>().Where(t => t.WorkNumber == workNumber).First()?.FullPathCode?.Split("_").Reverse().ToList();

                // 找到所有的人
                var deptcodes = _dbPlatform.Queryable<SFPositionUserRelEntity, SFApproverEntity>((a, b) => a.PositionCode == b.PositionCode)
                    .Where((a, b) => allDeptFullPathCodes.Contains(b.DeptCode) && b.RoleCode == "HRBP TD DOA").Select((a, b) => new { a.UserId, b.DeptCode }).ToList();

                if (deptcodes != null && deptcodes.Count() > 0)
                {
                    foreach (var deptcode in allDeptFullPathCodes)
                    {
                        if (deptcodes.Where(t => t.DeptCode == deptcode).Count() > 0)
                        {
                            worknumbers = deptcodes.Where(t => t.DeptCode == deptcode).Select(t => t.UserId).ToList();
                            break;
                        }
                    }
                }

                return worknumbers;
            }

            return _dbPlatform.Queryable<SFOtherApproverEntity>()
                .Where(p => p.RelationshipType == type.GetHashCode().ToString())
                .Where(p => p.UserId == workNumber)
                .Select(p => p.RelUserId).ToList();
        }

        /// <summary>
        /// 获取字典表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<Dictionaries>> GetKeyValues(GetKeyValuesInput input)
        {
            RefAsync<int> totalCount = 0;
            input.MaxResultCount = 200;

            var listDto = await _dbPlatform.Queryable<Dictionaries>()
                .WhereIF(!input.Code.IsNullOrWhiteSpace(), x => x.Code.Equals(input.Code))
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), x => x.Name.Contains(input.Name))
                .WhereIF(!input.ParentCode.IsNullOrWhiteSpace(), x => x.UpperId.Equals(input.ParentCode))
                .WhereIF(!input.CatalogCode.IsNullOrWhiteSpace(), x => x.TypeCode.Equals(input.CatalogCode))
                .OrderBy(x => x.OrderNum)
                .ToPageListAsync(1, input.MaxResultCount, totalCount);

            // 返回结果
            return new PagedResultDto<Dictionaries>()
            {
                TotalCount = totalCount,
                Items = listDto
            };
        }

        /// <summary>
        /// 发送邮件
        /// </summary>
        /// <param name="emailType">邮件类型</param>
        /// <param name="userName">发起人</param>
        /// <param name="workNumber">发起人工号</param>
        /// <param name="contractCode">合同编码</param>
        /// <returns></returns>
        public async Task SendEmail(ContractEmailTypeEnum emailType, string userName, string workNumber, string contractCode)
        {
            var topic = string.Empty;
            var templateName = string.Empty;
            var replaceContent = string.Empty;
            var content = string.Empty;

            // 默认给发起人
            string to = string.Empty;
            if (!string.IsNullOrEmpty(workNumber))
            {
                to = _dbPlatform.Queryable<Users>().Where(t => t.WorkNumber == workNumber).First()?.Email;
            }
            #region 拼接邮件内容
            switch (emailType)
            {
                // 未签合同提醒
                case ContractEmailTypeEnum.未签合同提醒:
                    {
                        topic = "[系统邮件，请勿回复]《劳动合同》签署情况跟进";
                        templateName = "UnSignedContract.cshtml";
                        var allUserContract = _dbPlatform.Queryable<UserContractEntity, UserExtension>((p, a) => p.WorkNumber == a.Sapemployeeid)
                            .Where((p, a) => p.ContractStatus == (int)ContractEnum.ContractStatusEnum.签署中 && p.CreateUserWorkNumber == workNumber)
                            .Select((p, a) => new { p.UserName, p.WorkNumber, a.Contractbegin, p.DepartName }).ToList();
                        allUserContract?.ForEach(x =>
                         {
                             replaceContent += @$"<tr>
                                                 <th>{x.UserName}</th>   
                                                 <th>{x.WorkNumber}</th>                          
                                                 <th>{x.Contractbegin}</th>                        
                                                 <th>{x.DepartName}</th>                              
                                                 </tr>";
                         });
                    }
                    break;
                // 合同电子签签署完成
                case ContractEmailTypeEnum.合同电子签签署完成:
                    {
                        topic = $"[系统邮件，请勿回复]{userName}《劳动合同》签署完成";
                        content = $"<div style=\"margin-left: 30px;\"> 您好，<br />" +
                            $"{userName}的《劳动合同》({contractCode})电子签已经完成并回传系统。</div>";
                    }
                    break;
                // 合同电子签签署作废
                case ContractEmailTypeEnum.合同电子签签署作废:
                    {
                        topic = $"[系统邮件，请勿回复]{userName}《劳动合同》签署已作废";
                        content = $"<div style=\"margin-left: 30px;\"> 您好，<br />" +
                            $"{userName}的《劳动合同》({contractCode})电子签已作废。</div>";
                    }
                    break;
                // 主体变更协议电子签签署完成
                case ContractEmailTypeEnum.主体变更协议电子签签署完成:
                    {
                        topic = $"[系统邮件，请勿回复]{userName}《主体变更协议》签署完成";
                        content = $"<div style=\"margin-left: 30px;\"> 您好，<br />" +
                            $"{userName}的《主体变更协议》({contractCode})电子签已经完成并回传系统。</div>";
                    }
                    break;
                // 主体变更协议电子签签署作废
                case ContractEmailTypeEnum.主体变更协议电子签签署作废:
                    {
                        topic = $"[系统邮件，请勿回复]{userName}《主体变更协议》签署已作废";
                        content = $"<div style=\"margin-left: 30px;\"> 您好，<br />" +
                            $"{userName}的《主体变更协议》({contractCode})电子签已作废。</div>";
                    }
                    break;
                // 合同到期提醒
                case ContractEmailTypeEnum.合同到期提醒:
                    {
                        topic = "[系统邮件，请勿回复]员工劳动合同到期提醒通知";
                        templateName = "ContractExpire.cshtml";
                        var date = DateTime.Now.AddDays(60);
                        var startdate = new DateTime(date.Year, date.Month, date.Day);
                        var enddate = startdate.AddDays(1).AddMinutes(-1);

                        var date1 = DateTime.Now.AddDays(45);
                        var startdate1 = new DateTime(date1.Year, date1.Month, date1.Day);
                        var enddate1 = startdate1.AddDays(1).AddMinutes(-1);

                        // 找到这些人对应的HRBP负责人
                        var allworkNumbers = this.GetUserByRoleFilter(workNumber, RelationshipTypeEnum.HRBP, true);

                        var allUserContract = _dbPlatform.Queryable<UserContractEntity>()
                            .Where(p => (p.ContractEndDate >= startdate && p.ContractEndDate <= enddate)
                            || (p.ContractEndDate >= startdate1 && p.ContractEndDate <= enddate1))
                            .Where(p => allworkNumbers.Contains(p.WorkNumber))
                            .Where(p => p.ContractStatus == (int)ContractEnum.ContractStatusEnum.履行中)
                            .ToList();
                        if (allUserContract == null || allUserContract.Count() == 0)
                        {
                            return;
                        }

                        allUserContract?.ForEach(x =>
                        {
                            replaceContent += @$"<tr>
                                                 <th>{x.UserName}</th>   
                                                 <th>{x.WorkNumber}</th>                          
                                                 <th>{x.DepartName}</th>                        
                                                 <th>{x.UserPosnc}</th>
                                                 <th>{x.ContractEndDate?.ToString("yyyy-MM-dd")}</th>
                                                 </tr>";
                        });
                    }
                    break;
                // 未签主体变更协议提醒
                case ContractEmailTypeEnum.未签主体变更协议提醒:
                    {
                        topic = "[系统邮件，请勿回复]《主体变更协议》签署情况跟进";
                        templateName = "UnsignedContractChange.cshtml";
                        var allUserContract = _dbPlatform.Queryable<UserContractTripartiteAgreementEntity, UserContractEntity>((p, a) => p.OriginalContractCode == a.ContractCode)
                            .Where((p, a) => p.ChangeStatus == (int)ContractEnum.ContractChangeStatusEnum.签署中 && p.CreateWorkNumber == workNumber)
                            .Select((p, a) => new { a.UserName, a.WorkNumber, p.OriginalContractCode, a.DepartName })
                            .ToList();
                        allUserContract?.ForEach(x =>
                        {
                            replaceContent += @$"<tr>
                                                 <th>{x.UserName}</th>   
                                                 <th>{x.WorkNumber}</th>    
                                                 <th>{x.DepartName}</th> 
                                                 <th>{x.OriginalContractCode}</th>                                                    
                                                 </tr>";
                        });
                    }
                    break;
                // 未发起合同提醒
                case ContractEmailTypeEnum.未发起合同提醒:
                    {
                        // 20231114 CR 立邦/立邦中国/立邦香港台湾 不发起提醒
                        DateTime startdate = new DateTime(2023, 8, 1);
                        DateTime estartdate = new DateTime(2024, 3, 1); // 实习生查询开始时间
                        topic = "[系统邮件，请勿回复]未发起劳动合同人员清单";
                        templateName = "UnStartContract.cshtml";
                        var usercodes = GetUserByRoleFilter(workNumber, RelationshipTypeEnum.ER, true);
                        var allUser = _dbPlatform.Queryable<Users, UserExtension, UserContractEntity>((x, a, p) =>
                        new JoinQueryInfos(
                            JoinType.Inner, x.WorkNumber == a.Sapemployeeid,
                            JoinType.Left, x.WorkNumber == p.WorkNumber))
                            .Where((x, a, p) =>
                            usercodes.Contains(a.Sapemployeeid)
                            && x.Status == 1
                            && p.ContractCode == null
                            && a.Status == "3"
                            && (a.Contract != "E" && DateTime.Parse(a.Contractbegin) >= startdate
                                || a.Contract == "E" && DateTime.Parse(a.Contractbegin) >= estartdate)
                            && a.Contractbegin != null
                            && a.Stext_2 != "立邦香港台湾")
                            .Select((x, a, p) => new { a.Name, a.Sapemployeeid, a.Contractbegin, a.Orgname }).ToList();
                        if (allUser == null || allUser.Count() == 0)
                        {
                            return;
                        }

                        var alluserNumbers = allUser.Select(t => t.Sapemployeeid).ToList();
                        var allUsers = _dbPlatform.Queryable<Users>().Where(t => alluserNumbers.Contains(t.WorkNumber)).Select(t => new { t.WorkNumber, t.FullPathText }).ToList();

                        allUser?.ForEach(x =>
                        {
                            var fullpathname = allUsers.FirstOrDefault(t => t.WorkNumber == x.Sapemployeeid)?.FullPathText;
                            fullpathname = string.IsNullOrEmpty(fullpathname) ? x.Orgname : fullpathname;
                            replaceContent += @$"<tr>
                                                 <th>{x.Name}</th>   
                                                 <th>{x.Sapemployeeid}</th>                          
                                                 <th>{x.Contractbegin}</th>                        
                                                 <th>{fullpathname}</th>                              
                                                 </tr>";
                        });
                    }
                    break;
                // 职级20及以上员工合同到期提醒
                case ContractEmailTypeEnum.职级20及以上员工合同到期提醒:
                    {
                        topic = "[系统邮件，请勿回复]员工劳动合同到期提醒通知";
                        templateName = "ContractExpire.cshtml";
                        var date = DateTime.Now.AddDays(60);
                        var startdate = new DateTime(date.Year, date.Month, date.Day);
                        var enddate = startdate.AddDays(1).AddMinutes(-1);

                        var date1 = DateTime.Now.AddDays(45);
                        var startdate1 = new DateTime(date1.Year, date1.Month, date1.Day);
                        var enddate1 = startdate1.AddDays(1).AddMinutes(-1);

                        var filterGrades = new List<string>();
                        for (var g = 20; g < 30; g++)
                        {
                            filterGrades.Add(g.ToString());
                        }

                        var allUserContract = _dbPlatform.Queryable<UserContractEntity, Users, UserExtension>((p, b, u) =>
                            p.WorkNumber == b.WorkNumber && p.WorkNumber == u.Sapemployeeid)
                            .Where((p, b, u) => (p.ContractEndDate >= startdate && p.ContractEndDate <= enddate)
                            || (p.ContractEndDate >= startdate1 && p.ContractEndDate <= enddate1))
                            .Where((p, b, u) => p.ContractStatus == (int)ContractStatusEnum.履行中 && b.Status == 1)
                            .Select((p, b, u) => new
                            {
                                p.UserName,
                                p.WorkNumber,
                                p.DepartName,
                                p.UserPosnc,
                                p.ContractEndDate,
                                u.Posnc
                            }).ToList();
                        allUserContract = allUserContract.Where(x =>
                            !string.IsNullOrEmpty(x.Posnc) && filterGrades.Any(q => x.Posnc.Contains(q))).ToList();
                        if (allUserContract == null || allUserContract.Count() == 0)
                        {
                            return;
                        }

                        allUserContract?.ForEach(x =>
                        {
                            replaceContent += @$"<tr>
                                                 <th>{x.UserName}</th>   
                                                 <th>{x.WorkNumber}</th>                          
                                                 <th>{x.DepartName}</th>                        
                                                 <th>{x.UserPosnc}</th>
                                                 <th>{x.ContractEndDate?.ToString("yyyy-MM-dd")}</th>
                                                 </tr>";
                        });
                    }
                    break;
            }
            #endregion
            if (!string.IsNullOrEmpty(templateName))
            {
                content = _emailAPPService.GenerateEmailTemplate(templateName);
                content = !string.IsNullOrEmpty(replaceContent) ?
                    content.Replace("{content}", replaceContent) : string.Empty;
            }
            if (!string.IsNullOrEmpty(content) && !string.IsNullOrEmpty(to) && ConfigManager.NeedSendEmail)
            {
                await _capBus.PublishAsync("bpmnew.EmailAppService.SendEmailAsync",
                  new SendEmailSimpleDto()
                  {
                      To = to,
                      Subject = topic,
                      Body = content
                  });
            }
        }

        /// <summary>
        /// 立邦企业微信通用通知消息WebAPI接口
        /// </summary>
        /// <param name="title">消息标题</param>
        /// <param name="message">消息内容</param>
        /// <param name="url">消息url</param>
        /// <param name="users">企业微信账户，多个用单竖线分割（不是工号）</param>
        /// <returns></returns>
        public async Task MsgSendForAPI(string title, string message, string url, string users)
        {
            var headers = new Dictionary<string, string>
                {
                    { "Content-Type", "application/json" },
                    { "requestId", Guid.NewGuid().ToString() },
                    { "trackId", Guid.NewGuid().ToString() },
                    { "sourceSystem", "BPMNEW" },
                    { "serviceName", "	S_XXX_WEBAPP_MsgSendForAPI_S" }
                };
            // 拼接dynamic模型
            var sendData = new
            {
                Systemid = "BPM_CommonNew",
                StaffAccount = users,
                Title = title,
                Message = message,
                Url = url
            };
            var result = await _nPRestHttp.ESBPostAsync(_configuration["Nippon:ESB:ESBCommApi"], JsonConvert.SerializeObject(sendData), headers);
        }

        /// <summary>
        /// 导出Excel模板
        /// </summary>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<MemoryStream> ExportTemplate(string name)
        {
            var fileName = Environment.CurrentDirectory + "/wwwroot/excel/" + name;
            if (File.Exists(fileName))
            {
                var fs = File.OpenRead(fileName);
                IWorkbook workbook = new XSSFWorkbook(fs);
                var memoryStream = new MemoryStream();
                workbook.Write(memoryStream);
                workbook.Close();
                return memoryStream;
            }
            else
            {
                throw new Exception(name + "文件模板未找到!");
            }
        }

        /// <summary>
        /// 获取流程步骤
        /// </summary>
        /// <param name="searchDto"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<List<ActivitieTaskModel>> GetInstancesSteps(string[] instanceNumber)
        {
            var activityData = await _dbEngine.Queryable<Activities, Tasks>((a, b) => new object[]
               {
                 JoinType.Left, a.ActivityId.ToString() == b.ActivityId
               }).Where(a => instanceNumber.Contains(a.InstanceNumber))
               .OrderBy(a => a.Order)
                .Select((a, b) => new ActivitieTaskModel
                {
                    InstanceId = a.InstanceId,
                    InstanceNumber = a.InstanceNumber,
                    Name = a.Name,
                    ArriveTime = a.ArriveTime,
                    ElapsedTime = a.ElapsedTime,
                    FinishTime = a.FinishTime,
                    UserName = b.UserName,
                    GrandNodeId = a.GrandNodeId,
                    ActivityId = a.ActivityId,
                    UserId = b.UserId
                }).ToListAsync();
            return activityData;
        }

    }
}
